package com.sihai.ehr.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang.math.NumberUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import com.sihai.core.DataAccessor;
import com.sihai.core.Limit;
import com.sihai.core.LimitedList;
import com.sihai.core.QueryCreator;
import com.sihai.core.Sorter;
import com.sihai.core.support.HibernateDataAccessor;
import com.sihai.ehr.model.biz.Duty;
import com.sihai.ehr.model.biz.ExamBaseConfig;
import com.sihai.ehr.model.sys.ExaminePeriodEnum;
import com.sihai.ehr.service.ExamBaseConfigService;
import com.sihai.ehr.web.pojo.ExamBaseConfigPojo;

/**
 * author: wxn since: 12-10-10 Time: 下午6:41
 * 
 * 考核指标设定Service
 */
@Service("examBaseConfigService")
public class ExamBaseConfigServiceImpl implements ExamBaseConfigService {
	@Resource
	private DataAccessor dataAccessor;

	public LimitedList<ExamBaseConfigPojo> findByTopOrganization(
			Long organizationId, Long dutyId, Limit limit, Sorter sorter) {
		String hql = "select new com.sihai.ehr.web.pojo.ExamBaseConfigPojo(d, c.id,c.percentageQualitative,c.percentageQuantitative,c.percentagePerformance,c.periodType) from ExamBaseConfig c right join c.duty d where d.cancel=0 and (d.organization.id = ? or d.organization.topOrganization.id = ?)";
		Object[] args = { organizationId, organizationId };
		if (dutyId != null && dutyId > 0) {
			hql = "select new com.sihai.ehr.web.pojo.ExamBaseConfigPojo(d, c.id,c.percentageQualitative,c.percentageQuantitative,c.percentagePerformance,c.periodType) from ExamBaseConfig c right join c.duty d where  d.cancel=0 and (d.organization.id = ? or d.organization.topOrganization.id = ?) and d.id=?";
			args = new Object[] { organizationId, organizationId, dutyId };
		}
		return dataAccessor.findByHql(ExamBaseConfigPojo.class, hql, args,
				limit, sorter);
	}

	public LimitedList<ExamBaseConfigPojo> searchQualConfigList(
			Long organizationId, Long dutyId, Limit limit, Sorter sorter) {

		LimitedList<ExamBaseConfigPojo> listBaseConfigPojos = new LimitedList<ExamBaseConfigPojo>();
		int totalCount = 0;
		StringBuffer strTotalString = new StringBuffer("SELECT count(1) from exam_base_config as c ");
		strTotalString.append(" RIGHT OUTER JOIN");
		strTotalString.append(" (select duty.id,o.full_name,duty.duty_name");
		strTotalString.append(" from duty,organization as o where duty.organization_id=o.id and duty.cancel<>1 ");
		if (organizationId != null && organizationId > 0) {
			strTotalString.append(" and instr(o.tree_path,',").append(organizationId)
					.append(",')>0 ");
		}
		if (dutyId != null && dutyId > 0) {
			strTotalString.append(" and duty.id=").append(dutyId);
		}
		strTotalString.append(") as d on c.duty_id=d.id");
		
		
		StringBuffer sb = new StringBuffer();
		sb.append("select c.id,d.full_name,d.duty_name,c.percentage_performance,c.percentage_finish,c.percentage_qualitative,c.percentage_quantitative,c.period_mode,c.period_type,(select count(1) from employeeinfo_work as w where c.duty_id=w.duty_id) as actual_nubmer from exam_base_config as c ");
		sb.append(" RIGHT OUTER JOIN");
		sb.append(" (select duty.id,o.full_name,duty.duty_name");
		sb.append(" from duty,organization as o where duty.organization_id=o.id and duty.cancel<>1 ");
		if (organizationId != null && organizationId > 0) {
			sb.append(" and instr(o.tree_path,',").append(organizationId)
					.append(",')>0 ");
		}
		if (dutyId != null && dutyId > 0) {
			sb.append(" and duty.id=").append(dutyId);
		}
		sb.append(") as d on c.duty_id=d.id");
		if (limit != null) {
			sb.append(" limit ").append(limit.getOffset()).append(",")
					.append(limit.getCount());
		}
		// 读取数据库
		Session vSession = ((HibernateDataAccessor) dataAccessor)
				.getHibernateTemplate().getSessionFactory()
				.getCurrentSession();
		Connection vConnection = vSession.connection();
		try {
			Statement vStatement = vConnection.createStatement();
			
			vStatement = vConnection.createStatement();
			ResultSet rsTotal = vStatement.executeQuery(strTotalString.toString());
			if (rsTotal.next()) {
				totalCount = rsTotal.getInt(1);
			}
			rsTotal.close();
			
			ResultSet rs = vStatement.executeQuery(sb.toString());
			while (rs.next()) {
				ExamBaseConfigPojo baseConfigPojo = new ExamBaseConfigPojo(
						rs.getLong("id"), rs.getString("full_name"),
						rs.getString("duty_name"),
						rs.getFloat("percentage_performance"),
						rs.getFloat("percentage_qualitative"),
						rs.getFloat("percentage_qualitative"),
						rs.getString("period_type") != null ? ExaminePeriodEnum
								.valueOf(rs.getString("period_type")) : null,
						rs.getInt("actual_nubmer"));

				listBaseConfigPojos.add(baseConfigPojo);
			}
			rs.close();
			vStatement.close();
			listBaseConfigPojos.setTotalcount(totalCount);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				vConnection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return listBaseConfigPojos;
	}

	public LimitedList<ExamBaseConfigPojo> findByOrganization(
			Long organizationId, Limit limit, Sorter sorter) {
		return dataAccessor
				.findByHql(
						ExamBaseConfigPojo.class,
						"select new com.sihai.ehr.web.pojo.ExamBaseConfigPojo(d, c.id, c.percentageQualitative,c.percentageQuantitative,c.percentagePerformance,c.periodType) from ExamBaseConfig c right join c.duty d where d.organization.id = ?",
						new Object[] { organizationId }, limit, sorter);
	}

	@Override
	public ExamBaseConfig get(Long id) {
		if (id == null) {
			return null;
		}
		return dataAccessor.get(ExamBaseConfig.class, id);
	}

	@Override
	public ExamBaseConfig getExamBaseConfigByDutyId(final Long dutyId) {
		if (dutyId == null || dutyId <= 0) {
			return null;
		}
		try {
			return dataAccessor.findUnique(ExamBaseConfig.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							String hql = "from ExamBaseConfig e where e.duty.id=:dutyId";
							Query query = session.createQuery(hql);
							query.setParameter("dutyId", dutyId);
							return query;
						}
					});
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取考核标准配置时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamBaseConfigServiceImpl.getExamBaseConfigByDutyId;参数dutyId="
							+ dutyId + "】");
		}
	}

	@Override
	public void save(ExamBaseConfig examBaseConfig) {
		if (examBaseConfig != null) {
			dataAccessor.save(examBaseConfig);
		}
	}

	@Override
	public void saveForBatch(ExamBaseConfig examBaseConfig, String batchType,
			String batchIds) {
		if ("create".equals(batchType)) {// 批量添加
			String[] dutyIds = batchIds.split(",");
			if (dutyIds == null || dutyIds.length == 0) {
				return;
			}
			for (String dutyId : dutyIds) {
				Duty duty = dataAccessor.get(Duty.class,
						NumberUtils.createLong(dutyId));
				if (duty != null) {
					// examBaseConfig.setDuty(duty);
					// examBaseConfig.setId(null);
					ExamBaseConfig config = new ExamBaseConfig();
					config.setDuty(duty);
					config.setPercentageQualitative(examBaseConfig
							.getPercentageQualitative());
					config.setPercentageQuantitative(examBaseConfig
							.getPercentageQuantitative());
					config.setPercentageFinish(examBaseConfig
							.getPercentageFinish());
					config.setPercentagePerformance(examBaseConfig
							.getPercentagePerformance());
					config.setPeriodType(examBaseConfig.getPeriodType());
					config.setPeriodMode(examBaseConfig.getPeriodMode());
					save(config);
				}
			}
		} else if ("edit".equals(batchType)) {// 批量修改
			String[] configIds = batchIds.split(",");
			if (configIds == null || configIds.length == 0) {
				return;
			}
			for (String configId : configIds) {
				ExamBaseConfig config = dataAccessor.get(ExamBaseConfig.class,
						NumberUtils.createLong(configId));
				if (config != null) {
					config.setPercentageQualitative(examBaseConfig
							.getPercentageQualitative());
					config.setPercentageQuantitative(examBaseConfig
							.getPercentageQuantitative());
					config.setPercentageFinish(examBaseConfig
							.getPercentageFinish());
					config.setPercentagePerformance(examBaseConfig
							.getPercentagePerformance());
					config.setPeriodType(examBaseConfig.getPeriodType());
					config.setPeriodMode(examBaseConfig.getPeriodMode());
					save(config);
				}
			}
		}
	}

	@Override
	public List<Long> searchCurExamDutysByDayLimit(
			ExaminePeriodEnum periodEnum, int day, boolean isExamCurrent) {
		List<Long> list = new ArrayList<Long>();
		StringBuffer sbSQLQuery = new StringBuffer();
		sbSQLQuery.append("select duty_id from exam_base_config ");
		sbSQLQuery.append(" where period_type='" + periodEnum.toString() + "'");
		if (isExamCurrent) {
			sbSQLQuery.append(" and (case period_mode ");
			sbSQLQuery
					.append(" when 0 then datediff(now(),LAST_DAY(DATE_SUB(NOW(),INTERVAL 1 MONTH))) <= "
							+ day);
			sbSQLQuery
					.append(" ELSE datediff(now(), date(concat(year(now()),'-',month(now()),'-',period_mode))) <= "
							+ day);
			sbSQLQuery.append(" end)");
		}
		Session vSession = ((HibernateDataAccessor) dataAccessor)
				.getHibernateTemplate().getSessionFactory().getCurrentSession();
		Connection vConnection = vSession.connection();
		Statement vStatement;
		try {
			vStatement = vConnection.createStatement();
			ResultSet rs = vStatement.executeQuery(sbSQLQuery.toString());
			while (rs.next()) {
				list.add(rs.getLong("duty_id"));
			}
			return list;
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取定性考核员工分数数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamBaseConfigServiceImpl.searchCurExamDutysByDayLimit;参数periodEnum="
							+ periodEnum + " 】");
		}
	}
}
